﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace R.DMS.Data
{
    public class CommonDAL
    {
        #region 合同

        /// <summary>
        /// 根据条件获取合同信息
        /// </summary>
        /// <param name="name">合同名称</param>
        /// <param name="customerNo">客户编号</param>
        /// <param name="customerName">客户名称 模糊搜索</param>
        /// <param name="status">状态</param>
        public List<ContractEntity> GetContractList(IDbConnection connection, string name, string customerNo, string customerName, string status, IDbTransaction transaction = null)
        {
            StringBuilder condition = new StringBuilder(" where 1=1 AND C_DeleteFlag=0 ");
            if (!string.IsNullOrWhiteSpace(name)) //名称
            {
                condition.Append(" AND C_ContractName LIKE @ContractName ");
                name = "%" + name + "%";
            }
            if (!string.IsNullOrWhiteSpace(customerNo)) //客户
            {
                condition.Append(" AND C_CustomerNo = @CustomerNo ");
            }
            if (!string.IsNullOrWhiteSpace(customerName)) //客户
            {
                condition.Append(" AND C_CustomerName LIKE @CustomerName ");
                customerName = "%" + customerName + "%";
            }
            if (!string.IsNullOrWhiteSpace(status))
            {
                condition.Append(" AND C_Status = @Status ");
            }

            return connection.GetList<ContractEntity>(condition.ToString(), new { ContractName = name, CustomerNo = customerNo, CustomerName = customerName, Status = status }, transaction).ToList();
        }

        /// <summary>
        /// 获取合同详情
        /// </summary>
        /// <param name="id">主键</param>
        public ContractEntity GetContract(IDbConnection connection, int id)
        {
            return connection.Get<ContractEntity>(id);
        }

        /// <summary>
        /// 新增合同信息
        /// </summary>
        public void CreateContract(IDbConnection connection, ContractEntity entity, IDbTransaction transaction = null)
        {
            connection.Insert(entity, transaction);
        }

        /// <summary>
        /// 更新合同信息
        /// </summary>
        public void UpdateContract(IDbConnection connection, ContractEntity entity, IDbTransaction transaction = null)
        {
            connection.Update(entity, transaction);
        }

        /// <summary>
        /// 删除合同信息
        /// </summary>
        public void DeleteContract(IDbConnection connection, int id, IDbTransaction transaction = null)
        {
            connection.Execute("UPDATE dbo.T_Contract SET C_DeleteFlag=1 WHERE C_Id=@Id ", new { Id = id }, transaction);
        }

        #endregion

        #region 项目

        /// <summary>
        /// 根据条件获取项目信息
        /// </summary>
        /// <param name="name">项目名称</param>
        /// <param name="customerNo">客户编号</param>
        /// <param name="customerName">客户名称 模糊搜索</param>
        /// <param name="status">状态</param>
        public List<ProjectEntity> GetProjectList(IDbConnection connection, string name, string customerNo, string customerName, string status, IDbTransaction transaction = null)
        {
            StringBuilder condition = new StringBuilder(" where 1=1 AND P_DeleteFlag=0 ");
            if (!string.IsNullOrWhiteSpace(name)) //名称
            {
                condition.Append(" AND P_ProjectName LIKE @ProjectName ");
                name = "%" + name + "%";
            }
            if (!string.IsNullOrWhiteSpace(customerNo)) //客户
            {
                condition.Append(" AND P_CustomerNo = @CustomerNo ");
            }
            if (!string.IsNullOrWhiteSpace(customerName)) //客户
            {
                condition.Append(" AND P_CustomerName LIKE @CustomerName ");
                customerName = "%" + customerName + "%";
            }
            if (!string.IsNullOrWhiteSpace(status))
            {
                condition.Append(" AND P_Status = @Status ");
            }

            return connection.GetList<ProjectEntity>(condition.ToString(), new { ProjectName = name, CustomerNo = customerNo, CustomerName = customerName, Status = status }, transaction).ToList();
        }

        /// <summary>
        /// 获取项目详情
        /// </summary>
        /// <param name="id">主键</param>
        public ProjectEntity GetProject(IDbConnection connection, int id)
        {
            return connection.Get<ProjectEntity>(id);
        }

        /// <summary>
        /// 新增项目信息
        /// </summary>
        public void CreateProject(IDbConnection connection, ProjectEntity entity, IDbTransaction transaction = null)
        {
            connection.Insert(entity, transaction);
        }

        /// <summary>
        /// 更新项目信息
        /// </summary>
        public void UpdateProject(IDbConnection connection, ProjectEntity entity, IDbTransaction transaction = null)
        {
            connection.Update(entity, transaction);
        }

        /// <summary>
        /// 删除项目信息
        /// </summary>
        public void DeleteProject(IDbConnection connection, int id, IDbTransaction transaction = null)
        {
            connection.Execute("UPDATE dbo.T_Project SET P_DeleteFlag=1 WHERE P_Id=@Id ", new { Id = id }, transaction);
        }

        #endregion

        #region 专利

        /// <summary>
        /// 根据条件获取专利信息
        /// </summary>
        /// <param name="name">专利名称</param>
        /// <param name="type">专利类型</param>
        /// <param name="customerNo">客户编号</param>
        /// <param name="customerName">客户名称 模糊搜索</param>
        /// <param name="status">状态</param>
        /// <param name="year">申请年份</param>
        public List<PatentEntity> GetPatentList(IDbConnection connection, string name, string type, string customerNo, string customerName, string status, string year, IDbTransaction transaction = null)
        {
            StringBuilder condition = new StringBuilder(" where 1=1  AND Pa_DeleteFlag=0 ");
            if (!string.IsNullOrWhiteSpace(name)) //名称
            {
                condition.Append(" AND Pa_PatentName LIKE @PatentName ");
                name = "%" + name + "%";
            }
            if (!string.IsNullOrWhiteSpace(type)) //类型
            {
                condition.Append(" AND Pa_PatentType = @PatentType ");
            }
            if (!string.IsNullOrWhiteSpace(customerNo)) //客户
            {
                condition.Append(" AND Pa_CustomerNo = @CustomerNo ");
            }
            if (!string.IsNullOrWhiteSpace(customerName)) //客户
            {
                condition.Append(" AND Pa_CustomerName LIKE @CustomerName ");
                customerName = "%" + customerName + "%";
            }
            if (!string.IsNullOrWhiteSpace(status))
            {
                condition.Append(" AND Pa_Status = @Status ");
            }
            if (!string.IsNullOrWhiteSpace(year))
            {
                condition.Append(" AND YEAR(Pa_ApplyDate) = @Year ");
            }

            return connection.GetList<PatentEntity>(condition.ToString(), new { PatentName = name, PatentType = type, CustomerNo = customerNo, CustomerName = customerName, Status = status, Year = year }, transaction).ToList();
        }

        /// <summary>
        /// 获取专利详情
        /// </summary>
        /// <param name="id">主键</param>
        public PatentEntity GetPatent(IDbConnection connection, int id)
        {
            return connection.Get<PatentEntity>(id);
        }

        /// <summary>
        /// 新增专利信息
        /// </summary>
        public void CreatePatent(IDbConnection connection, PatentEntity entity, IDbTransaction transaction = null)
        {
            connection.Insert(entity, transaction);
        }

        /// <summary>
        /// 更新专利信息
        /// </summary>
        public void UpdatePatent(IDbConnection connection, PatentEntity entity, IDbTransaction transaction = null)
        {
            connection.Update(entity, transaction);
        }

        /// <summary>
        /// 删除专利信息
        /// </summary>
        public void DeletePatent(IDbConnection connection, int id, IDbTransaction transaction = null)
        {
            connection.Execute("UPDATE dbo.T_Patent SET Pa_DeleteFlag=1 WHERE Pa_Id=@Id ", new { Id = id }, transaction);
        }

        #endregion

        #region 政策

        /// <summary>
        /// 根据条件获取政策信息
        /// </summary>
        /// <param name="name">政策名称</param>
        /// <param name="year">政策年份</param>
        public List<PolicyEntity> GetPolicyList(IDbConnection connection, string name, string year, IDbTransaction transaction = null)
        {
            StringBuilder condition = new StringBuilder(" where 1=1  AND Po_DeleteFlag=0 ");
            if (!string.IsNullOrWhiteSpace(name)) //名称
            {
                condition.Append(" AND Po_PolicyName LIKE @PolicyName ");
                name = "%" + name + "%";
            }
            if (!string.IsNullOrWhiteSpace(year)) //年份
            {
                condition.Append(" AND Po_Year = @Year ");
            }

            return connection.GetList<PolicyEntity>(condition.ToString(), new { PolicyName = name, Year = year }, transaction).ToList();
        }

        /// <summary>
        /// 获取政策详情
        /// </summary>
        /// <param name="id">主键</param>
        public PolicyEntity GetPolicy(IDbConnection connection, int id)
        {
            return connection.Get<PolicyEntity>(id);
        }

        /// <summary>
        /// 新增政策信息
        /// </summary>
        public void CreatePolicy(IDbConnection connection, PolicyEntity entity, IDbTransaction transaction = null)
        {
            connection.Insert(entity, transaction);
        }

        /// <summary>
        /// 更新政策信息
        /// </summary>
        public void UpdatePolicy(IDbConnection connection, PolicyEntity entity, IDbTransaction transaction = null)
        {
            connection.Update(entity, transaction);
        }

        /// <summary>
        /// 删除政策信息
        /// </summary>
        public void DeletePolicy(IDbConnection connection, int id, IDbTransaction transaction = null)
        {
            connection.Execute("UPDATE dbo.T_Policy SET Po_DeleteFlag=1 WHERE Po_Id=@Id ", new { Id = id }, transaction);
        }

        #endregion

        #region 文件

        /// <summary>
        /// 根据条件获取文件信息
        /// </summary>
        /// <param name="name">文件名称</param>
        /// <param name="relatedId">文件关联外键</param>
        /// <param name="type">文件类型</param>
        public List<UploadFilesEntity> GetFileList(IDbConnection connection, string name, string relatedId, string type, IDbTransaction transaction = null)
        {
            StringBuilder condition = new StringBuilder(" where 1=1 AND F_DeleteFlag=0 ");
            if (!string.IsNullOrWhiteSpace(name)) //名称
            {
                condition.Append(" AND F_FileName LIKE @FileName ");
                name = "%" + name + "%";
            }
            if (!string.IsNullOrWhiteSpace(relatedId)) //外键
            {
                condition.Append(" AND F_RelatedId = @RelatedId ");
            }
            if (!string.IsNullOrWhiteSpace(type)) //类型
            {
                condition.Append(" AND F_FileType = @FileType ");
            }

            return connection.GetList<UploadFilesEntity>(condition.ToString(), new { FileName = name, RelatedId = relatedId, FileType = type }, transaction).ToList();
        }

        /// <summary>
        /// 获取文件详情
        /// </summary>
        /// <param name="id">主键</param>
        public UploadFilesEntity GetFile(IDbConnection connection, int id)
        {
            return connection.Get<UploadFilesEntity>(id);
        }

        /// <summary>
        /// 新增文件信息
        /// </summary>
        public void CreateFile(IDbConnection connection, UploadFilesEntity entity, IDbTransaction transaction = null)
        {
            connection.Insert(entity, transaction);
        }

        /// <summary>
        /// 更新文件信息
        /// </summary>
        public void UpdateFile(IDbConnection connection, UploadFilesEntity entity, IDbTransaction transaction = null)
        {
            connection.Update(entity, transaction);
        }

        /// <summary>
        /// 删除文件信息
        /// </summary>
        public void DeleteFile(IDbConnection connection, int id, IDbTransaction transaction = null)
        {
            connection.Execute("UPDATE dbo.T_UploadFiles SET F_DeleteFlag=1 WHERE F_Id=@Id ", new { Id = id }, transaction);
        }

        #endregion

    }
}
